package prove;

import java.sql.*;

import java.io.*;
import java.util.*;

public class Database {
	private Connection conn;
	public Database() throws SQLException, IOException{
		conn=getConnection();
	}
	
	public void esempio() throws SQLException, IOException{
		      try{
		        Statement stat = conn.createStatement(); 
		        stat.execute("CREATE TABLE Greetings (Message VARCHAR(20))");
		        stat.execute("INSERT INTO Greetings VALUES ('Hello, World!')");
		        ResultSet result = stat.executeQuery("SELECT * FROM Greetings");
		        result.next();
		        System.out.println(result.getString(1)+" "+result.getString(2));
		      }catch(SQLException e){
					 System.out.println("Sintassi errata");
				 }
	 }
	 
	 public void iniz(String nomeDatabase) throws SQLException, IOException{
		 try{
			 Statement stat=conn.createStatement();
			 String creazione="CREATE TABLE "+nomeDatabase+"(" +
			 		"nome VARCHAR(20) NOT NULL," +
			 		"cognome VARCHAR(20) NOT NULL," +
			 		"datadinascita VARCHAR(10)," +
			 		"comunedinascita VARCHAR(20)," +
			 		"provincianascita VARCHAR(2)," +
			 		"residenza VARCHAR(20)," +
			 		"provinciaresidenza VARCHAR(2)," +
			 		"tipodocumento VARCHAR(20)," +
			 		"numerodocumento VARCHAR(15)," +
			 		"rilasciatoda VARCHAR(30)," +
			 		"datarilascio VARCHAR(10)," +
			 		"PRIMARY KEY (numerodocumento))";
			 stat.execute(creazione);
		 }catch(SQLException e){
			 System.out.println(e.getMessage());
		 }
	 }
	 
	 public void query(String query) throws SQLException, IOException{
		 try{
			 Statement stat=conn.createStatement();
			 stat.execute(query);
		 }catch(SQLException e){
			 System.out.println(e.getMessage());
		 }
	 }
	 
	 public void chiudiConnessione() throws SQLException{
		 conn.close();
	 }
	 
	 public void stampaQuery(String query) throws SQLException, IOException{
		 try{
			Statement stat=conn.createStatement();
			ResultSet result = stat.executeQuery(query);
			while(result.next()){
				System.out.println(result.getString(1));//mostra solo nome e cognome
			}
		 }catch(SQLException e){
			 System.out.println(e.getMessage());
		 }
	 }
	 
	 public Connection getConnection() throws SQLException, IOException{  
		      Properties props = new Properties();
		      FileInputStream in = new FileInputStream("database.properties");
		      props.load(in);
		      in.close();
		      String drivers = props.getProperty("jdbc.drivers");
		      String url = props.getProperty("jdbc.url");
		      String username = props.getProperty("jdbc.username");
		      String password = props.getProperty("jdbc.password");
		      if (drivers != null)
		         System.setProperty("jdbc.drivers", drivers);
		      return DriverManager.getConnection(url, username, password);
		   }
}		      

